*This file creates summary stats data tables and figures

********************************************************************************
*Figure 1 Data
********************************************************************************

*what percentage of markets had independent regionals being used by year?
*How many markets do each regional airline carrier operate in in each year 
use regressionsdataset.dta, clear

sort year quarter market tkcarrier 

keep tkcarrier year market onenotowned
duplicates drop

gen regional_used = onenotowned
keep tkcarrier year market regional_used 

collapse(max) regional_used, by(market year tkcarrier)

gen sum_step = 1
sort year market

bysort year: egen total_mkts_carrier_combos = sum(sum_step)
bysort year: egen total_ind_reg_mkts_carrier = sum(regional_used)

gen regional_mkt_pct = total_ind_reg_mkts_carrier / total_mkts_carrier_combos
keep year regional_mkt_pct
duplicates drop
rename regional_mkt_pct indregional_mkt_pct

********************************************************************************
*Figure 2 Data
********************************************************************************

*Percentage of passengers transported via regionals by majors
use regressionsdataset.dta, clear
sort year quarter market tkcarrier 

gen segment_path = segment1 + "_" + segment2 + "_" + segment3

preserve
gen regional1 = unowned_regional1+owned_regional1
keep tkcarrier market year mktpass_tkopcarrier opcarrier1 segment_path regional1
*keep tkcarrier market year mktpass_tkopcarrier opcarrier1 segment_path unowned_regional1
rename opcarrier1 opcarrier
rename regional1 regional
*rename unowned_regional1 regional
keep if regional == 1
keep tkcarrier market opcarrier year mktpass_tkopcarrier segment_path
save segment1data.dta, replace
restore

preserve
gen regional2 = unowned_regional2+owned_regional2
keep tkcarrier market year mktpass_tkopcarrier opcarrier2 regional2 segment_path
*keep tkcarrier market year mktpass_tkopcarrier opcarrier2 unowned_regional2 segment_path
rename opcarrier2 opcarrier
rename regional2 regional
*rename unowned_regional2 regional
keep if regional == 1
keep tkcarrier market opcarrier year mktpass_tkopcarrier segment_path
save segment2data.dta, replace
restore

gen regional3 = unowned_regional3+owned_regional3
keep tkcarrier market year mktpass_tkopcarrier opcarrier3 regional3 segment_path
*keep tkcarrier market year mktpass_tkopcarrier opcarrier3 unowned_regional3 segment_path
rename opcarrier3 opcarrier
rename regional3 regional
*rename unowned_regional3 regional
keep if regional == 1

append using segment1data
append using segment2data

erase segment1data.dta
erase segment2data.dta

drop opcarrier regional 
sort year market tkcarrier segment_path
duplicates drop

bysort tkcarrier year: egen regional_pass = sum(mktpass_tkopcarrier)

keep tkcarrier year regional_pass
duplicates drop

save regional_pass.dta, replace

use regressionsdataset.dta, clear
sort year quarter market tkcarrier 

bysort tkcarrier year: egen total_pass = sum(mktpass_tkopcarrier)
keep tkcarrier year total_pass
duplicates drop

merge 1:1 tkcarrier year using regional_pass
drop _merge
erase regional_pass.dta

gen regional_pct = regional_pass/total_pass

keep tkcarrier year regional_pct

keep if tkcarrier == "AA" | tkcarrier == "AS" | tkcarrier == "CO" | tkcarrier == "DL" | tkcarrier == "NW" | tkcarrier == "UA" | tkcarrier == "US"

*reshape wide
reshape wide regional_pct, i(year) j(tkcarrier) string

********************************************************************************
*Figure 3 Data
********************************************************************************

use finaldatasetforregressions.dta, clear
*drop monopoly
drop if monopoly==1

keep year market tkcarrier pct_major_ind
duplicates drop

collapse(mean) pct_major_ind, by(year)

********************************************************************************
*Table 4 Summary Stats Data
********************************************************************************

use finaldatasetforregressions.dta, clear
drop if monopoly == 1

keep year market tkcarrier mmc_ek csc_avg pct_major_ind logprice markets_served_origin_TKT markets_served_dest_TKT
replace markets_served_origin_TKT = markets_served_origin_TKT/100
replace markets_served_dest_TKT = markets_served_dest_TKT/100

summarize logprice pct_major_ind markets_served_origin_TKT markets_served_dest, detail 
gen price = exp(logprice)
summarize price, detail

keep market year mmc_ek csc_avg 
duplicates drop

summarize mmc_ek, detail

summarize csc_avg, detail


********************************************************************************
*Figure 5 Data
********************************************************************************

use finaldatasetforregressions.dta, clear

sort market

*replace monopoly market entries with 0's
replace mmc_ek = 0 if monopoly == 1
replace csc_avg = 0 if monopoly == 1

drop if monopoly == 1

keep market year mmc_ek csc_avg csc_count_avg csc_weighted_avg 
duplicates drop

sort year market
export delimited using mkt_year_mmc_csc.csv, replace

********************************************************************************
*Table A.2.1 Data
********************************************************************************

use weekly_weather_data.dta, clear

ge quarter=1 if month==1 | month==2 | month==3
replace quarter=2 if month==4 | month==5 | month==6
replace quarter=3 if month==7 | month==8 | month==9
replace quarter=4 if month==10 | month==11 | month==12

drop month day week date
drop wsf2-wt16

*replace missing snow variables
replace snow = 0 if missing(snow)
replace snwd = 0 if missing(snwd)

foreach var of varlist prcp snow snwd tmax tmin {
       bysort airport year quarter: egen mean`var'=mean(`var')
	   drop `var'
	   rename mean`var' `var'
}

duplicates drop

save temp_weather, replace

use finaldatasetforregressions.dta, clear

keep market

gen airport = substr(market,1,3)
keep airport
duplicates drop

merge 1:m airport using temp_weather
keep if _merge == 3
erase temp_weather.dta

summarize prcp, detail
summarize snow, detail
summarize snwd, detail
summarize tmin, detail



































